<?php
/*
 * DB2 Adapter
 */
 
class DB2Adapter {
	
	protected $db, $host, $user, $password, $dbName, $port; 
	public $errno, $error, $insertId, $affectedRows;
	
	public function __construct($dbcPar) {
		$this->host = $dbcPar['host'];
		$this->user = $dbcPar['user'];
		$this->password = $dbcPar['password'];
		$this->dbName = $dbcPar['dbName'];		
		$this->port = intval($dbcPar['port']);
		$this->schema = $dbcPar['schema'];
		$this->insertId = 0;
		$this->affectedRows = 0;
		$this->errno = 0;
		$this->error = '';
		$this->connect();
	}	
	
	public function connect() 	{
		if ($this->db) { return true; }
		$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=".$this->dbName.";" .
  							"HOSTNAME=".$this->host.";PORT=".$this->port.";PROTOCOL=TCPIP;" .
  							"UID=".$this->user.";PWD=".$this->password.";";
  		$aOpz = array("DB2_ATTR_CASE"=>DB2_CASE_LOWER);
		$this->db = db2_connect($conn_string, '', '', $aOpz);
		if($this->db){
			$this->errno = db2_conn_error($this->db);
			$this->error = db2_conn_errormsg($this->db);	
			if ($this->errno != 0) return false;
			$res = @db2_exec($this->db, "SET SCHEMA ".$this->schema);	
			$this->errno = db2_stmt_error();
			$this->error = db2_stmt_errormsg();
			if ($this->errno != 0) return false;
		}else{
			$this->errno = "1";
			$this->error = "Connessione non effettuata:<br><b>$conn_string</b>";
		}
		return true;
	}
	
	public function close() 	{
		if (!$this->db) { return false; }
		db2_close($this->db);
		$this->db = NULL;
		return true;
	}
	
 	function query($sql, $aOpt='') {
 		if($this->connect()){
 			$sql = str_replace("\\'","''",$sql);
 			$sql = stripslashes($sql);
			$query = @db2_exec($this->db, $sql);
			$this->errno = db2_stmt_error();
			$this->error = db2_stmt_errormsg();
			if ($this->errno != 0) return false;
	 		return $query;
 		}else{
 			return false;
 		}
 	}
	
 	function fetch($query, $pos='') {
 		if($pos != "" && is_numeric($pos)){
 			$row = db2_fetch_assoc($query, $pos);
 		}else{
 			$row = db2_fetch_assoc($query);
 		}	
 		return $row;
 	}
	
 	function free($query) {
 		db2_free_result($query);	
 		return true;
 	}
	
 	function insert($entita, $aAttrib, $alias, $aOpt='') {
 		$this->connect();
 		$sql = "INSERT INTO ".$entita." SET ";
 		foreach($aAttrib as $nomeAttr => $valAttr) {
 			if ($nomeAttr[0] != $alias) { continue; }
 			$sql .= substr($nomeAttr,2)." = '".str_replace("'","''",$valAttr)."', "; 
 		}
 		$sql = substr($sql,0,-2);
		$query = db2_exec($this->db, $sql);
		$this->errno = db2_stmt_error ($query);
		$this->error = db2_stmt_errormsg($query);	
		$this->insertId = $this->getLastInsertId($entita);	
		if ($this->errno != 0) return false;
		$this->free($query);
		return true;
 	}
	
 	function update($entita, $aAttrib, $alias, $aKey, $aOpt='') {
 		$this->connect();
 		$sql = "UPDATE ".$entita." SET ";
 		foreach($aAttrib as $nomeAttr => $valAttr) {
 			if ($nomeAttr[0] != $alias) { continue; }
 			if (in_array($nomeAttr,$aKey)) { continue; }
 			$sql .= substr($nomeAttr,2)." = '".str_replace("'","''",$valAttr)."', "; 
 		}
 		$sql = substr($sql,0,-2)." WHERE ";
 		foreach($aKey as $kk => $nomeAttr) {
 			$sql .= substr($nomeAttr,2)." = '".str_replace("'","''",$aAttrib[$nomeAttr])."' AND "; 
 		}
 		$sql = substr($sql,0,-5);
		$query = db2_exec($this->db, $sql);
		$this->errno = db2_stmt_error ($query);
		$this->error = db2_stmt_errormsg($query);	
		if ($this->errno != 0) return false;
		$this->affectedRows = db2_num_rows($this->db);
		$this->free($query);
		return true;
 	}
 	
 	function delete($entita, $aAttrib, $aKey, $aOpt='') {
 		$this->connect();
 		$sql = "DELETE FROM ".$entita." WHERE ";
 		foreach($aKey as $kk => $nomeAttr) {
 			$sql .= substr($nomeAttr,2)." = '".str_replace("'","''",$aAttrib[$nomeAttr])."' AND "; 
 		}
 		$sql = substr($sql,0,-5);
		$query = db2_exec($this->db, $sql);
		$this->errno = db2_stmt_error ($query);
		$this->error = db2_stmt_errormsg($query);		
		if ($this->errno != 0) return false;
		$this->affectedRows = db2_num_rows($this->db);
		//db2_free_result($query);
		return true;
 	}
 	
 	function read($entita, &$aAttrib, $alias, $aKey, $aOpt='') {
 		// $aOpt['lock'] = 'yes' : esegue select with lock
 		// $aOpt['locktype'] = "upd" : lock type per Update 		
 		// $aOpt['locktype'] = "shr" : lock type tipo shared
 		$this->connect();
 		$sql = "SELECT ";
		foreach($aAttrib as $nomeAttr => $valAttr) {
 			if ($nomeAttr[0] != $alias) { continue; }
 			//if (in_array($nomeAttr,$aKey)) { continue; }
 			$sql .= substr($nomeAttr,2).", "; 
 		}
 		
		$sql = substr($sql,0,-2)." FROM ".$entita." WHERE ";
 		foreach($aKey as $kk => $nomeAttr) {
 			$sql .= substr($nomeAttr,2)." = '".str_replace("'","''",$aAttrib[$nomeAttr])."' AND "; 
 		}
 		$sql = substr($sql,0,-5);
 		if(isset($aOpt['lock']) && $aOpt['lock'] == "yes"){
 				//$sql .= ";\n LOCK TABLE $entita IN SHARE MODE";
 		}
 		//echo("<br>SQL: $sql<br><br>");
		$query = db2_exec($this->db, $sql);
		$row = db2_fetch_assoc($query);
		if($row){
			foreach($aAttrib as $nomeAttr => $valAttr) {
	 			if ($nomeAttr[0] != $alias) { continue; }
	 			if (in_array($nomeAttr,$aKey)) { continue; }
	 			$aAttrib[$nomeAttr] = $row[substr($nomeAttr,2)]; 
	 		}
		}
		$this->errno = db2_stmt_error ($query);
		$this->error = db2_stmt_errormsg($query);	
		if ($this->errno != 0) return false;
		$this->affectedRows = db2_num_rows($this->db);
		$this->free($query);
		return true;
 	}
 	
 	function getFields($entita){
 		$sSQL = "SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='".strtoupper($entita)."' AND TBCREATOR='".strtoupper($this->schema)."';";
 		$aFields = array();
 		if($query = $this->query($sSQL)){
 			while($row = $this->fetch($query)){
 				$aFields[strtolower($row["name"])] = array("Type"=>trim($row["coltype"])); 
 			}
 			return $aFields;
 		}else{
 			echo("ERRORE ".$this->error);
 			return false;
 		}
 	}
 	
 	function startTransaction() {
 		if(db2_autocommit($this->db, FALSE)){
 			return true;
 		}else{ 				
	 		$this->errno = db2_stmt_error ();
			$this->error = db2_stmt_errormsg();	
			return false;	
 		}
 	}
 	
 	function commit() {
 		if(db2_commit($this->db)){
 			return true;
 		}else{ 				
	 		$this->errno = db2_stmt_error ();
			$this->error = db2_stmt_errormsg();	
			return false;	
 		}
 	}
 	
 	function rollback() {
 		if(db2_rollback($this->db)){
 			return true;
 		}else{ 				
	 		$this->errno = db2_stmt_error ();
			$this->error = db2_stmt_errormsg();	
			return false;	
 		}
 	}
 	
 	function getLastInsertId($entita){
 		$sql = "SELECT SYSIBM.IDENTITY_VAL_LOCAL() AS id FROM " . $entita;
 		$query = $this->query($sql);
 		if($row = $this->fetch($query)){
 			return $row["id"];
 		}else{
 			return false;
 		}
 	}
}
?>
